﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace BusinessLayer
{
    public class Author
    {
        private string _firstName, _lastName, _authorName;
        private int _birthYear, _Aid;

        public string FirstName { get { return this._firstName; } set { this._firstName = value; }    }
        public string LastName { get { return this._lastName; } set { this._lastName = value; }     }
        public string AuthorName { get { return this._authorName; } set { this._authorName = value; } }
        public int BirthYear { get { return this._birthYear; } set { this._birthYear = (int)value; } }
        public int Aid { get { return this._Aid; } set { this._Aid = (int)value; } }

        public Author() { }

        public Author(string firstName, string lastName, int birthYear)
        {
            _firstName = firstName;
            _lastName = lastName;
            _birthYear = birthYear;
        }

        public List<Book> getBooks()
        {
            return Book.getByAuthor(this._Aid);
        }

        //public static List<string> getAidByISBN(string isbn)
        //{
        //    string sql =
        //        "SELECT AUTHOR.Aid FROM AUTHOR INNER JOIN BOOK_AUTHOR ON BOOK_AUTHOR.Aid = AUTHOR.Aid " +
        //        "WHERE BOOK_AUTHOR.ISBN=@ISBN ";

        //    List<string> result = new List<string>();
        //    SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
        //    SqlCommand cmd = new SqlCommand(sql, con);
        //    cmd.Parameters.Add(new SqlParameter("ISBN", isbn));
        //    try
        //    {
        //        con.Open();
        //        SqlDataReader dar = cmd.ExecuteReader();
        //        while (dar.Read())
        //        {
        //            result.Add(dar["Aid"].ToString());                   
        //        }
        //    }
        //    catch (Exception er)
        //    {
        //        throw er;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //    return result;
        //}


        public static Author getByAid(int aid)
        {
            Author newAuthor = null;
            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand("SELECT * FROM AUTHOR WHERE Aid=" + aid, con);
            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                if (dar.Read())
                {
                    newAuthor = new Author();
                    newAuthor.Aid = (int)dar["Aid"];
                    newAuthor.BirthYear = (dar["BirthYear"] == DBNull.Value) ? 0 : Convert.ToInt32(dar["BirthYear"].ToString());
                    newAuthor.FirstName = dar["FirstName"] as string;
                    newAuthor.LastName = dar["LastName"] as string;
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }
            return newAuthor;
        }

        public static List<Author> getByName(string name)
        {
            string sql =
                "SELECT AUTHOR.Aid, AUTHOR.BirthYear, AUTHOR.FirstName + ' ' + AUTHOR.LastName AS AuthorName FROM AUTHOR " +
                "WHERE AUTHOR.FirstName + ' ' + AUTHOR.LastName LIKE '%" + name + "%' ";

            List<Author> result = new List<Author>();
            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
                SqlDataReader dar = cmd.ExecuteReader();
                while (dar.Read())
                {
                    Author newAuthor = new Author();
                    newAuthor.Aid = (int)dar["Aid"];
                    newAuthor.BirthYear = (dar["BirthYear"] == DBNull.Value) ? 0 : Convert.ToInt32(dar["BirthYear"].ToString());
                    newAuthor.AuthorName = dar["AuthorName"] as string;
                    result.Add(newAuthor);
                }
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        public static List<Author> GetAll()
        {
            List<Author> authorList = new List<Author>();
            SqlConnection dbCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand dbCmd = new SqlCommand("SELECT * FROM AUTHOR",dbCon);
            try
            {
                dbCon.Open();
                SqlDataReader dbDar = dbCmd.ExecuteReader();

                while (dbDar.Read())
                {
                        Author readAuthor = new Author();
                        readAuthor.Aid = (int)dbDar["Aid"];

                        if (dbDar["FirstName"] == DBNull.Value) { readAuthor.FirstName = ""; }
                        else { readAuthor.FirstName = (string)dbDar["FirstName"]; }
                        if (dbDar["LastName"] == DBNull.Value) { readAuthor.LastName = ""; }
                        else { readAuthor.LastName = (string)dbDar["LastName"]; }
                        if (dbDar["BirthYear"] == DBNull.Value) { readAuthor.BirthYear = 0; }
                        else{ readAuthor.BirthYear = Convert.ToInt32( dbDar["BirthYear"].ToString() ); }

                        authorList.Add(readAuthor);
                }

            }
            catch (Exception Exc) { throw Exc; }
            finally { 
                dbCon.Close(); }

            return authorList;
        }
        //Save new author
        public static void SaveAuthor(string FirstName, string LastName, int BirthYear)
        { 

            SqlConnection dbCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            
            SqlCommand dbCmd = new SqlCommand("INSERT INTO dbo.AUTHOR (FirstName,LastName,BirthYear) VALUES (@FirstName,@LastName,@BirthYear)" ,dbCon);

            dbCmd.Parameters.Add(new SqlParameter("FirstName", FirstName));
            dbCmd.Parameters.Add(new SqlParameter("LastName", LastName));
            dbCmd.Parameters.Add(new SqlParameter("BirthYear", BirthYear));
            try
            { 
                dbCon.Open();                
                dbCmd.ExecuteReader();
            }
            catch (Exception Exc) { throw Exc; }
            finally { dbCon.Close(); }

        }
        public static void ModAuthor(int Aid, string FirstName, string LastName, int BirthYear)
        {
            SqlConnection dbCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand dbCmd = new SqlCommand("UPDATE dbo.AUTHOR SET FirstName=@FS, LastName=@LS, BirthYear=@BY WHERE Aid=@Aid ", dbCon);
            dbCmd.Parameters.Add(new SqlParameter("Aid", Aid));
            dbCmd.Parameters.Add(new SqlParameter("FS", FirstName));
            dbCmd.Parameters.Add(new SqlParameter("LS", LastName));
            dbCmd.Parameters.Add(new SqlParameter("BY", BirthYear));
            try
            {
                dbCon.Open();
                dbCmd.ExecuteReader();
            }
            catch (Exception Exc) { throw Exc; }
            finally { dbCon.Close(); }
        }

        public static void DeleteAuthor(int Aid)
        {
            SqlConnection dbCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
            SqlCommand dbCmd = new SqlCommand("DELETE b FROM BOOK as a JOIN BOOK_AUTHOR as b on a.ISBN = b.ISBN WHERE b.Aid=@Aid;", dbCon);
            dbCmd.Parameters.Add(new SqlParameter("Aid", Aid));
            try
            {
                dbCon.Open();
                dbCmd.ExecuteReader();
            }
            catch (Exception Exc) { throw Exc; }
            finally 
            { 
                dbCon.Close();

                SqlConnection con2 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
                SqlCommand cmd2 = new SqlCommand("DELETE FROM AUTHOR WHERE Aid=@Aid;", con2);
                cmd2.Parameters.Add(new SqlParameter("Aid", Aid));
                try
                {
                    con2.Open();
                    SqlDataReader dar2 = cmd2.ExecuteReader();
                }
                catch (Exception er)
                {
                    throw er;
                }
                finally
                {
                    con2.Close();
                }                
            }
        }
    }
}